package com.example.hello.mapper;

import com.example.hello.entity.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

/**
 * 员工Mapper接口
 */
@Mapper
public interface EmpMapper {
    
    /**
     * 查询所有员工（包含部门名称）
     */
    @Select("SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id ORDER BY e.update_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "name", column = "name"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "position", column = "position"),
        @Result(property = "salary", column = "salary"),
        @Result(property = "img", column = "img"),
        @Result(property = "hireDate", column = "hire_date"),
        @Result(property = "deptId", column = "dept_id"),
        @Result(property = "deptName", column = "dept_name"),
        @Result(property = "createTime", column = "create_time"),
        @Result(property = "updateTime", column = "update_time")
    })
    List<Emp> selectAll();
    
    /**
     * 根据ID查询员工（包含部门名称和工作经历）
     */
    @Select("SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "name", column = "name"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "position", column = "position"),
        @Result(property = "salary", column = "salary"),
        @Result(property = "img", column = "img"),
        @Result(property = "hireDate", column = "hire_date"),
        @Result(property = "deptId", column = "dept_id"),
        @Result(property = "deptName", column = "dept_name"),
        @Result(property = "createTime", column = "create_time"),
        @Result(property = "updateTime", column = "update_time"),
        @Result(property = "workExperiences", column = "id", many = @Many(select = "com.example.hello.mapper.EmpExprMapper.selectByEmpId"))
    })
    Emp selectById(@Param("id") Integer id);
    
    /**
     * 分页查询员工列表（包含部门名称）
     */
    @Select("<script>" +
            "SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id " +
            "<where>" +
            "<if test='name != null and name != \"\"'>" +
            "AND e.name LIKE CONCAT('%', #{name}, '%')" +
            "</if>" +
            "<if test='username != null and username != \"\"'>" +
            "AND e.username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='gender != null'>" +
            "AND e.gender = #{gender}" +
            "</if>" +
            "<if test='startDate != null'>" +
            "AND e.hire_date &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null'>" +
            "AND e.hire_date &lt;= #{endDate}" +
            "</if>" +
            "</where>" +
            "ORDER BY e.update_time DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "name", column = "name"),
        @Result(property = "gender", column = "gender"),
        @Result(property = "phone", column = "phone"),
        @Result(property = "position", column = "position"),
        @Result(property = "salary", column = "salary"),
        @Result(property = "img", column = "img"),
        @Result(property = "hireDate", column = "hire_date"),
        @Result(property = "deptId", column = "dept_id"),
        @Result(property = "deptName", column = "dept_name"),
        @Result(property = "createTime", column = "create_time"),
        @Result(property = "updateTime", column = "update_time")
    })
    List<Emp> selectByPage(@Param("name") String name, @Param("username") String username, @Param("gender") Integer gender,
                          @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate,
                          @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);
    
    /**
     * 根据用户名查询
     */
    @Select("SELECT * FROM emp WHERE username = #{username} LIMIT 1")
    Emp selectByUsername(@Param("username") String username);
    
    /**
     * 查询总记录数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM emp e " +
            "<where>" +
            "<if test='name != null and name != \"\"'>" +
            "AND e.name LIKE CONCAT('%', #{name}, '%')" +
            "</if>" +
            "<if test='username != null and username != \"\"'>" +
            "AND e.username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='gender != null'>" +
            "AND e.gender = #{gender}" +
            "</if>" +
            "<if test='startDate != null'>" +
            "AND e.hire_date &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null'>" +
            "AND e.hire_date &lt;= #{endDate}" +
            "</if>" +
            "</where>" +
            "</script>")
    Long selectTotal(@Param("name") String name, @Param("username") String username, @Param("gender") Integer gender,
                    @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 添加员工
     */
    @Insert("INSERT INTO emp (username, password, name, gender, phone, position, salary, img, hire_date, dept_id, create_time, update_time) " +
            "VALUES (#{username}, #{password}, #{name}, #{gender}, #{phone}, #{position}, #{salary}, #{img}, #{hireDate}, #{deptId}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Emp emp);
    
    /**
     * 修改员工
     */
    @Update("UPDATE emp SET username = #{username}, password = #{password}, name = #{name}, gender = #{gender}, " +
            "phone = #{phone}, position = #{position}, salary = #{salary}, img = #{img}, hire_date = #{hireDate}, " +
            "dept_id = #{deptId}, update_time = #{updateTime} WHERE id = #{id}")
    int update(Emp emp);
    
    /**
     * 删除员工
     */
    @Delete("DELETE FROM emp WHERE id = #{id}")
    int deleteById(@Param("id") Integer id);
    
    /**
     * 更新员工头像
     */
    @Update("UPDATE emp SET img = #{img}, update_time = #{updateTime} WHERE id = #{id}")
    int updateAvatar(@Param("id") Integer id, @Param("img") String img, @Param("updateTime") java.time.LocalDateTime updateTime);
}
